package Controllers;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;






import java.sql.Statement;

import Entities.ServerEntity;
import GUI.ServerGUI;
import Server.ObservableOriginatorServer;

/**
 * This is the server controller.
 * @author Ofek-az
 *
 */
public class ServerController implements Validate {
	/**
	 * This is the server boundary.
	 */
	private ServerGUI serverGui;
	/**
	 * This is the server entity.
	 */
	private ServerEntity serverEnt;
	/**
	 * This the observable server.
	 */
	boolean serverConnected=false;
public static ObservableOriginatorServer observableOriginatorServer;

public ServerController(ServerEntity serverEnt)
{
	this.serverEnt=serverEnt;
	//autoStartServer(4000,"root","root");
}
/**
 * This is the ServerController constructor.
 * @param serverGui This is the server boundary.
 * @param serverEnt This is the server entity.
 */
public ServerController(ServerGUI serverGui,ServerEntity serverEnt) {
	this.serverGui = serverGui;
	this.serverEnt=serverEnt;
	this.serverGui.addConnectActionListener(new ServerStartListener());
	try {
		this.serverGui.getHostLabel().setText(InetAddress.getLocalHost().getHostName());
	} catch (UnknownHostException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

/**
 * This is the connect button listener class.
 * @author Ofek-az
 *
 */
class ServerStartListener implements ActionListener
{
	 
	/* (non-Javadoc)
	 * @see java.awt.event.ActionListener#actionPerformed(java.awt.event.ActionEvent)
	 */
	@Override
	public void actionPerformed(ActionEvent e) {
		if(!serverConnected && validateInput())
		startServer();
	}

}
public boolean autoStartServer(int port,String dbUser,String dbPass)
{

	try {		//create server
		observableOriginatorServer = new ObservableOriginatorServer(port);
		observableOriginatorServer.listen();
		observableOriginatorServer.addObserver(serverEnt);
		serverConnected=true;
	} catch (IOException e1) {
		System.out.println("Please enter another port.");
		return false;
		
	}
	if(serverConnected)
	{
		try 
		{		//connect to database
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {return false;}
		try 
		{
			
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/CCRM",dbUser,dbPass);
			serverEnt.setConn(conn);
			System.out.println("SQL connection succeed");
			//createDB(conn);
			
		} catch (SQLException ex) 
 	    {/* handle any errors*/
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
        return false;
        }
		
		
	}
	return true;
}
private void startServer()
{
	
	
	
		try {		//create server
			observableOriginatorServer = new ObservableOriginatorServer(Integer.parseInt(serverGui.getPortTf().getText()));
			observableOriginatorServer.listen();
			observableOriginatorServer.addObserver(serverEnt);
			serverConnected=true;
		} catch (IOException e1) {
			serverGui.displayWarningMessage("Please enter another port.");
			
		}
		if(serverConnected)
		{
			try 
			{		//connect to database
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			} catch (Exception ex) {/* handle the error*/}
			try 
			{
				String dbUser=serverGui.getDbUserTf().getText();
				String dbPass=serverGui.getDbPassTf().getText();
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/CCRM",dbUser,dbPass);
				serverEnt.setConn(conn);
				System.out.println("SQL connection succeed");
				//createDB(conn);
				serverGui.setVisible(false);
			} catch (SQLException ex) 
     	    {/* handle any errors*/
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            }
			
			
		}
	
}
public void createDB(Connection con1){
	Statement stmt;
	try {
		stmt = con1.createStatement();
		stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS `ccrm`"); 
		stmt.executeUpdate("USE `ccrm`"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `authorizations` (`cid` varchar(10) NOT NULL,`pid` varchar(10) NOT NULL,PRIMARY KEY (`cid`,`pid`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `customers` (`cid` varchar(10) NOT NULL,`importancerank` varchar(2) NOT NULL DEFAULT '5',`interestlevel` varchar(2) NOT NULL DEFAULT '5',PRIMARY KEY (`cid`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `distributions` (`domain` varchar(45) NOT NULL,`type` varchar(45) NOT NULL DEFAULT '',`product` varchar(45) NOT NULL DEFAULT '',`comments` varchar(45) DEFAULT NULL,PRIMARY KEY (`domain`,`type`,`product`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `employees` (`eid` varchar(10) NOT NULL,`position` varchar(3) NOT NULL,PRIMARY KEY (`eid`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `livingareas` (`name` varchar(45) NOT NULL,PRIMARY KEY (`name`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `customersresponsestooffer` (`sid` int(11) NOT NULL,`cid` varchar(10) NOT NULL,`pid` int(11) NOT NULL,`quantity` int(6) NOT NULL,`customerResponse` varchar(200) NOT NULL,PRIMARY KEY (`sid`,`cid`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `mp` (`mpid` int(11) NOT NULL AUTO_INCREMENT,`pid` int(11) NOT NULL,`pname` varchar(45) NOT NULL,`age` varchar(3) NOT NULL,`livingarea` varchar(45) NOT NULL,`importancerank` varchar(2) NOT NULL,`interestlevel` varchar(2) NOT NULL,`message` varchar(45) NOT NULL,PRIMARY KEY (`mpid`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `potentialcustomers` (`cid` varchar(10) NOT NULL,`password` varchar(45) NOT NULL,`name` varchar(45) NOT NULL,`livingArea` varchar(45) NOT NULL,`age` int(11) NOT NULL,`phoneNumber` varchar(45) NOT NULL,`email` varchar(45) NOT NULL, `loginStatus` int(1) NOT NULL,PRIMARY KEY (`cid`),KEY `livingarea_idx` (`livingArea`),CONSTRAINT `livingarea` FOREIGN KEY (`livingArea`) REFERENCES `livingareas` (`name`) ON DELETE NO ACTION ON UPDATE NO ACTION)"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `products` (`pid` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(45) NOT NULL,`description` varchar(45) NOT NULL,`price` varchar(45) NOT NULL,`image` varchar(200) NOT NULL,PRIMARY KEY (`pid`),UNIQUE KEY `pid_UNIQUE` (`pid`))"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `sales` (`sid` int(11) NOT NULL AUTO_INCREMENT,`mpid` int(11) NOT NULL,`date` varchar(45) NOT NULL,`period` varchar(45) NOT NULL,`location` varchar(45) NOT NULL,PRIMARY KEY (`sid`,`mpid`,`date`,`period`,`location`),UNIQUE KEY `sid_UNIQUE` (`sid`),KEY `marketingPat_idx` (`mpid`),CONSTRAINT `marketingPat` FOREIGN KEY (`mpid`) REFERENCES `mp` (`mpid`) ON DELETE NO ACTION ON UPDATE NO ACTION)"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `servesinsale` (`sid` int(11) NOT NULL,`eid` varchar(10) NOT NULL,`cid` varchar(10) NOT NULL,PRIMARY KEY (`sid`,`eid`,`cid`),KEY `eid_idx` (`eid`),KEY `cid_idx` (`cid`),CONSTRAINT `cid` FOREIGN KEY (`cid`) REFERENCES `customers` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT `eid` FOREIGN KEY (`eid`) REFERENCES `employees` (`eid`) ON DELETE NO ACTION ON UPDATE NO ACTION)"); 
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `users` (`uid` varchar(10) NOT NULL,`password` varchar(45) NOT NULL,`name` varchar(45) NOT NULL,`livingArea` varchar(45) NOT NULL,`age` int(11) NOT NULL,`loginStatus` int(1) NOT NULL,`phoneNumber` varchar(45) NOT NULL,`email` varchar(45) NOT NULL,PRIMARY KEY (`uid`),KEY `livingArea_idx` (`livingArea`),CONSTRAINT `lArea` FOREIGN KEY (`livingArea`) REFERENCES `livingareas` (`name`) ON DELETE NO ACTION ON UPDATE NO ACTION)"); 

		stmt.executeUpdate("load data local infile \"authorizations.txt\" into table authorizations");
		stmt.executeUpdate("load data local infile \"customers.txt\" into table customers");
		stmt.executeUpdate("load data local infile \"distributions.txt\" into table distributions");
		stmt.executeUpdate("load data local infile \"employees.txt\" into table employees");
		stmt.executeUpdate("load data local infile \"livingareas.txt\" into table livingareas");
		stmt.executeUpdate("load data local infile \"customersresponsestooffer.txt\" into table customersresponsestooffer");
		stmt.executeUpdate("load data local infile \"mp.txt\" into table mp");
		stmt.executeUpdate("load data local infile \"potentialcustomers.txt\" into table potentialcustomers");
		stmt.executeUpdate("load data local infile \"products.txt\" into table products");
		stmt.executeUpdate("load data local infile \"users.txt\" into table users");
 		
	} catch (SQLException e) {	e.printStackTrace();}
	 		
}
/* (non-Javadoc)
 * @see Controllers.Validate#validateInput()
 */
@Override
public boolean validateInput()
{
	String port=serverGui.getPortTf().getText();
	if(port.equals(""))
	{
		serverGui.displayWarningMessage("Please enter a port.");
		return false;
	}
	else
	{
		try
		{
		Integer.parseInt(port);
		
		return true;
		}
		catch (NumberFormatException e)
		{
			
			serverGui.displayWarningMessage("Error:illeagel characters in port field.");
			return false;
		}
		
	}
	
	
}
public ServerGUI getServerGui() {
	return serverGui;
}
public void setServerGui(ServerGUI serverGui) {
	this.serverGui = serverGui;
}
public ServerEntity getServerEnt() {
	return serverEnt;
}
public void setServerEnt(ServerEntity serverEnt) {
	this.serverEnt = serverEnt;
}
public static ObservableOriginatorServer getObservableOriginatorServer() {
	return observableOriginatorServer;
}
public static void setObservableOriginatorServer(
		ObservableOriginatorServer observableOriginatorServer) {
	ServerController.observableOriginatorServer = observableOriginatorServer;
}


}
